package opd.db.services.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.transaction.annotation.Transactional;

import opd.db.services.AbstractJDBCTemplateDao;
import opd.db.services.IReport1Dao;
import opd.domain.Report1;

public class Report1Dao extends AbstractJDBCTemplateDao implements IReport1Dao {

	// -------------------- //
	// -- SQL Statements -- // ----------------------------------------------------------------------------
	// -------------------- //

    private final String SQL_GET_DAILY_REPORT = 
    		"SELECT WORKINGDATE, DAILYUTILIZATION, "
    		+ "GAMMA_ENTERSGREEN+GAMMA_ENTERSBLUE+GAMMA_ENTERSRED+ALPHA_BETA_ENTERSGREEN+ALPHA_BETA_ENTERSBLUE+ALPHA_BETA_ENTERSRED"
    		+ " AS TOTAL_ENTERS,"
    		+ " GAMMA_ENTERSGREEN+ALPHA_BETA_ENTERSGREEN AS GREEN, GAMMA_ENTERSBLUE+ALPHA_BETA_ENTERSBLUE BLUE,"
    		+ " GAMMA_ENTERSRED+ALPHA_BETA_ENTERSRED AS RED, TOTAL_ENTRIES, TOTAL_ENTRIES_SUBPROJECT_B, TOTAL_ENTRIES_SUBPROJECT_D, "
    		+ " TOTAL_ENTRIES_SUBPROJECT_E,  WORKINGDATE2, TR.TARGET_UTILIZATION,"
    		+ " GAMMARATE, TARGET_GREEN, TARGET_BLUE, TARGET_RED, ABSENT, TARGET_GREEN2"
    		+ " FROM DAILY_REPORT4 DR4 INNER JOIN TARGETS TR ON DR4.TARGET_ID = TR.TARGET_ID"
    		+ " WHERE  WORKINGDATE2 between ?"
    		+ " AND ? AND WORKINGDATE2 IS NOT NULL"
    		+ " ORDER BY WORKINGDATE";
	

    private final String SQL_GET_AVERAGE_DAILY_REPORT = 
    		"SELECT  '' AS WORKINGDATE, AVG(DAILYUTILIZATION) AS DAILYUTILIZATION,"
    		+ " AVG (GAMMA_ENTERSGREEN+GAMMA_ENTERSBLUE+GAMMA_ENTERSRED+ALPHA_BETA_ENTERSGREEN+ALPHA_BETA_ENTERSBLUE+ALPHA_BETA_ENTERSRED)  AS TOTAL_ENTERS,"
    		+ " AVG (GAMMA_ENTERSGREEN+ALPHA_BETA_ENTERSGREEN) AS GREEN, AVG (GAMMA_ENTERSBLUE+ALPHA_BETA_ENTERSBLUE) BLUE,"
    		+ " AVG (GAMMA_ENTERSRED+ALPHA_BETA_ENTERSRED) AS RED, "
    		+ " AVG (TOTAL_ENTRIES) AS TOTAL_ENTRIES,"
    		+ " AVG (TOTAL_ENTRIES_SUBPROJECT_B) AS TOTAL_ENTRIES_SUBPROJECT_B,"
    		+ " AVG (TOTAL_ENTRIES_SUBPROJECT_D) AS TOTAL_ENTRIES_SUBPROJECT_D,"
    		+ " AVG (TOTAL_ENTRIES_SUBPROJECT_E) AS TOTAL_ENTRIES_SUBPROJECT_E,"
    		// + " GETDATE() AS WORKINGDATE2,"
    		+ " MAX(WORKINGDATE2) AS WORKINGDATE2,"
    		+ " AVG(TR.TARGET_UTILIZATION) AS TARGET_UTILIZATION,"
    		+ " AVG (GAMMARATE) AS GAMMARATE, "
    		+ " AVG(TARGET_GREEN) AS TARGET_GREEN,"
    		+ " AVG(TARGET_BLUE) AS TARGET_BLUE,"
    		+ " AVG(TARGET_RED) AS TARGET_RED, "
    		+ " AVG(ABSENT) AS ABSENT, "
    		+ " AVG(TARGET_GREEN2) " 
    		+ " FROM DAILY_REPORT4 DR4 INNER JOIN TARGETS TR ON DR4.TARGET_ID = TR.TARGET_ID"
    		+ " WHERE  WORKINGDATE2 between ?"
    		+ " AND ? AND WORKINGDATE2 IS NOT NULL";    
	
	
	// ---------------- //
	// -- Row Mapper -- // --------------------------------------------------------------------------------
	// ---------------- //
    
    class Report1RowMapper implements RowMapper<Report1>{

		@Override
		public Report1 mapRow(ResultSet rs, int row) throws SQLException {
			Report1 report1 = new Report1();
			
            report1.setWorkingdate(rs.getString("WORKINGDATE"));
            report1.setDailyUtilization(rs.getInt("DAILYUTILIZATION"));
            report1.setTotalEnters(rs.getInt("TOTAL_ENTERS"));
            report1.setGreen(rs.getInt("GREEN"));
            report1.setBlue(rs.getInt("BLUE"));
            report1.setRed(rs.getInt("RED"));
            report1.setTotalEntries(rs.getInt("TOTAL_ENTRIES"));
            report1.setTotalEntriesForSubprojectB(rs.getInt("TOTAL_ENTRIES_SUBPROJECT_B"));
            report1.setTotalEntriesForSubprojectD(rs.getInt("TOTAL_ENTRIES_SUBPROJECT_D"));
            report1.setTotalEntriesForSubprojectE(rs.getInt("TOTAL_ENTRIES_SUBPROJECT_E"));
            report1.setWorkingdateAsDate(rs.getDate("WORKINGDATE2"));
            report1.setTargetOfTheDay(rs.getInt("TARGET_UTILIZATION"));
            report1.setGammarate(rs.getInt("GAMMARATE"));
            report1.setTargetGreen(rs.getInt("TARGET_GREEN"));
            report1.setTargetBlue(rs.getInt("TARGET_BLUE"));
            report1.setTargetRed(rs.getInt("TARGET_RED"));
            report1.setAbsent(rs.getInt("ABSENT"));			
			
			return report1;
		}
    	
    }
    
	// ------------------------- //
	// -- Prepared Statements -- // ------------------------------------------------------------------------------
	// ------------------------- //

    class Report1PreparedStatement implements PreparedStatementCreator{
    	private String sqlStatement;
    	private Date dateFrom, dateTo;
    	
    	// ------------------------------------------------------------------------------------------------
    	
		public Report1PreparedStatement(String sqlStatement, Date dateFrom, Date dateTo) {
			this.sqlStatement = sqlStatement;
			this.dateFrom = dateFrom;
			this.dateTo = dateTo;
		}
		
		// -----------------------------------------------------------------------------------------------

		@Override
		public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
			PreparedStatement preparedStatement = connection.prepareStatement(this.sqlStatement);
			
			//preparedStatement.setDate(1, java.sql.Date.valueOf(this.dateFrom.toString()));
			
			preparedStatement.setDate(1, new java.sql.Date(this.dateFrom.getTime()));
			preparedStatement.setDate(2, new java.sql.Date(this.dateTo.getTime()));
			
			return preparedStatement;
		}
    } 
    
    // ----------------------------------------------------------------------------------------------------

	@Override
	@Transactional
	public List<Report1> getReport1(Date dateFrom, Date dateTo) {
		return jdbcTemplate.query(new Report1PreparedStatement(SQL_GET_DAILY_REPORT, dateFrom, dateTo), new Report1RowMapper());
	}

	// ----------------------------------------------------------------------------------------------------
	
	@Override
	@Transactional
	public Report1 getAverageReport1(Date dateFrom, Date dateTo) {
		List<Report1> report1List = jdbcTemplate.query(new Report1PreparedStatement(SQL_GET_AVERAGE_DAILY_REPORT, dateFrom, dateTo), new Report1RowMapper());
		
		if (report1List!=null && report1List.size() > 0) {
			return report1List.get(0);		
		}
		
		return new Report1();
	}

}
